/** Author: David Powell
This file takes the raw NVSS data and extracts the relevant variables
It then constructs the overdose measures (see line 862)
**/


clear all
set more off
set mat 11000
set maxvar 32500


global dir "/jules/b/dpowell/"
global RDATA "${dir}/purdue/replication/rawdata/"
global SDATA "${dir}/purdue/replication/data_cleaning/cleaned/"



# delimit ;

foreach num of numlist 17 16 14 15 {;
infix res_status 20 year 102-105 
str state_occ 21-22  
manner 107
str county_occ 23-25
str state_res 29-30
str county_res 35-37
month 65-66
str sex 69
str race 445-446
str hispanic 488
age_type 70
age 71-73
educ 61-62
educ2 63
educ3 64
str icd10 146-149
numberofconditions 163-164
str condition1 167-171
str condition2 174-178
str condition3 181-185
str condition4 188-192
str condition5 195-199
str condition6 202-206
str condition7 209-213
str condition8 216-220
str condition9 223-227
str condition10 230-234
str condition11 237-241
str condition12 244-248
str condition13 251-255
str condition14 258-262
str condition15 265-269
str condition16 272-276
str condition17 279-283
str condition18 286-290
str condition19 293-297
str condition20 300-304

str r_condition1 344-347
str r_condition2 349-352
str r_condition3 354-357
str r_condition4 359-362
str r_condition5 364-367
str r_condition6 369-372
str r_condition7 374-377
str r_condition8 379-382
str r_condition9 384-387
str r_condition10 389-392
str r_condition11 394-397
str r_condition12 399-402
str r_condition13 404-407
str r_condition14 409-412
str r_condition15 414-417
str r_condition16 419-422
str r_condition17 424-427
str r_condition18 429-432
str r_condition19 434-437
str r_condition20 439-442


using ${RDATA}/MULT20`num'.USAllCnty.txt;
save ${SDATA}mort20`num', replace;
clear;
};


foreach num of numlist 13/11 {;
infix res_status 20 year 102-105 
str state_occ 21-22  
str county_occ 23-25
str state_res 29-30
str county_res 35-37
month 65-66
str sex 69
str race 445-446
str hispanic 488
age_type 70
age 71-73
educ 61-62
educ2 63
educ3 64
str icd10 146-149
str condition1 167-171
str condition2 174-178
str condition3 181-185
str condition4 188-192
str condition5 195-199
str condition6 202-206
str condition7 209-213
str condition8 216-220
str condition9 223-227
str condition10 230-234
str condition11 237-241
str condition12 244-248
str condition13 251-255
str condition14 258-262
str condition15 265-269
str condition16 272-276
str condition17 279-283
str condition18 286-290
str condition19 293-297
str condition20 300-304

str r_condition1 344-347
str r_condition2 349-352
str r_condition3 354-357
str r_condition4 359-362
str r_condition5 364-367
str r_condition6 369-372
str r_condition7 374-377
str r_condition8 379-382
str r_condition9 384-387
str r_condition10 389-392
str r_condition11 394-397
str r_condition12 399-402
str r_condition13 404-407
str r_condition14 409-412
str r_condition15 414-417
str r_condition16 419-422
str r_condition17 424-427
str r_condition18 429-432
str r_condition19 434-437
str r_condition20 439-442

using ${RDATA}/MULT20`num'.USAllCnty.txt;
save ${SDATA}mort20`num', replace;
clear;
};



infix res_status 20 year 102-105 
str state_occ 21-22  
str county_occ 23-25
str state_res 29-30
str county_res 35-37
month 65-66
day 67-68
str sex 69
str race 445-446
str hispanic 488
age_type 70
age 71-73
educ 61-62
educ2 63
educ3 64
str icd10 146-149
str condition1 167-171
str condition2 174-178
str condition3 181-185
str condition4 188-192
str condition5 195-199
str condition6 202-206
str condition7 209-213
str condition8 216-220
str condition9 223-227
str condition10 230-234
str condition11 237-241
str condition12 244-248
str condition13 251-255
str condition14 258-262
str condition15 265-269
str condition16 272-276
str condition17 279-283
str condition18 286-290
str condition19 293-297
str condition20 300-304


str r_condition1 344-347
str r_condition2 349-352
str r_condition3 354-357
str r_condition4 359-362
str r_condition5 364-367
str r_condition6 369-372
str r_condition7 374-377
str r_condition8 379-382
str r_condition9 384-387
str r_condition10 389-392
str r_condition11 394-397
str r_condition12 399-402
str r_condition13 404-407
str r_condition14 409-412
str r_condition15 414-417
str r_condition16 419-422
str r_condition17 424-427
str r_condition18 429-432
str r_condition19 434-437
str r_condition20 439-442

using ${RDATA}/MULT2010.USAllCnty.txt;
save ${SDATA}mort2010, replace;



foreach num of numlist 9/5 {;
clear;
infix res_status 20 year 102-105 
str state_occ 21-22  
str county_occ 23-25
str state_res 29-30
str county_res 35-37
month 65-66
str sex 69
str race 445-446
str hispanic 488
age_type 70
age 71-73
str marital 84
str birthplace 55-56
educ 61-62
educ2 63
educ3 64
str icd10 146-149
str condition1 167-171
str condition2 174-178
str condition3 181-185
str condition4 188-192
str condition5 195-199
str condition6 202-206
str condition7 209-213
str condition8 216-220
str condition9 223-227
str condition10 230-234
str condition11 237-241
str condition12 244-248
str condition13 251-255
str condition14 258-262
str condition15 265-269
str condition16 272-276
str condition17 279-283
str condition18 286-290
str condition19 293-297
str condition20 300-304


str r_condition1 344-347
str r_condition2 349-352
str r_condition3 354-357
str r_condition4 359-362
str r_condition5 364-367
str r_condition6 369-372
str r_condition7 374-377
str r_condition8 379-382
str r_condition9 384-387
str r_condition10 389-392
str r_condition11 394-397
str r_condition12 399-402
str r_condition13 404-407
str r_condition14 409-412
str r_condition15 414-417
str r_condition16 419-422
str r_condition17 424-427
str r_condition18 429-432
str r_condition19 434-437
str r_condition20 439-442

using ${RDATA}/MULT200`num'.USAllCnty.txt;
save ${SDATA}mort200`num', replace;
};



foreach num of numlist 4/3 {;
clear;
infix res_status 20 placeofdeath 83 dayofweek 85 year 102-105 
str state_occ 21-22  
manner 107
str county_occ 23-25
str state_res 29-30
str county_res 35-37
str city_res 38-42
str citypop 43
month 65-66
str sex 69
str race 445-446
str hispanic 488
age_type 70
age 71-73
str marital 84
str birthplace 55-56
educ 61-62
educ2 63
educ3 64
str icd10 146-149
cause358 150-152
cause113 154-156
cause130 157-159
cause39 160-161
numberofconditions 163-164
str condition1 167-171
str condition2 174-178
str condition3 181-185
str condition4 188-192
str condition5 195-199
str condition6 202-206
str condition7 209-213
str condition8 216-220
str condition9 223-227
str condition10 230-234
str condition11 237-241
str condition12 244-248
str condition13 251-255
str condition14 258-262
str condition15 265-269
str condition16 272-276
str condition17 279-283
str condition18 286-290
str condition19 293-297
str condition20 300-304

str r_condition1 344-347
str r_condition2 349-352
str r_condition3 354-357
str r_condition4 359-362
str r_condition5 364-367
str r_condition6 369-372
str r_condition7 374-377
str r_condition8 379-382
str r_condition9 384-387
str r_condition10 389-392
str r_condition11 394-397
str r_condition12 399-402
str r_condition13 404-407
str r_condition14 409-412
str r_condition15 414-417
str r_condition16 419-422
str r_condition17 424-427
str r_condition18 429-432
str r_condition19 434-437
str r_condition20 439-442

using ${RDATA}/MULT200`num'.USAllCnty.txt;
save ${SDATA}mort200`num', replace;
};

foreach num of numlist 2 0 {;
clear;
infix res_status 20 year 115-118
str state_occ 21-22  
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
educ 52-53
str icd10 142-145
str condition1 164-168
str condition2 171-175
str condition3 178-182
str condition4 185-189
str condition5 192-196
str condition6 199-203
str condition7 206-210
str condition8 213-217
str condition9 220-224
str condition10 227-231
str condition11 234-238
str condition12 241-245
str condition13 248-252
str condition14 255-259
str condition15 262-266
str condition16 269-273
str condition17 276-280
str condition18 283-287
str condition19 290-294
str condition20 297-301

str r_condition1 341-345
str r_condition2 346-350
str r_condition3 351-355
str r_condition4 356-360
str r_condition5 361-366
str r_condition6 366-370
str r_condition7 371-375
str r_condition8 376-380
str r_condition9 381-385
str r_condition10 386-390
str r_condition11 391-395
str r_condition12 396-400
str r_condition13 401-405
str r_condition14 406-410
str r_condition15 411-415
str r_condition16 416-420
str r_condition17 421-425
str r_condition18 426-430
str r_condition19 431-435
str r_condition20 436-440


using ${RDATA}/MULT200`num'.USAllCnty.txt;
save ${SDATA}mort200`num', replace;
};

foreach num of numlist 1 {;
clear;
infix res_status 20 year 115-118
str state_occ 21-22  
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
str marital 77
str birthplace 78-79
educ 52-53
str icd10 142-145
str condition1 164-168
str condition2 171-175
str condition3 178-182
str condition4 185-189
str condition5 192-196
str condition6 199-203
str condition7 206-210
str condition8 213-217
str condition9 220-224
str condition10 227-231
str condition11 234-238
str condition12 241-245
str condition13 248-252
str condition14 255-259
str condition15 262-266
str condition16 269-273
str condition17 276-280
str condition18 283-287
str condition19 290-294
str condition20 297-301

str r_condition1 341-345
str r_condition2 346-350
str r_condition3 351-355
str r_condition4 356-360
str r_condition5 361-366
str r_condition6 366-370
str r_condition7 371-375
str r_condition8 376-380
str r_condition9 381-385
str r_condition10 386-390
str r_condition11 391-395
str r_condition12 396-400
str r_condition13 401-405
str r_condition14 406-410
str r_condition15 411-415
str r_condition16 416-420
str r_condition17 421-425
str r_condition18 426-430
str r_condition19 431-435
str r_condition20 436-440


using ${RDATA}/MULT200`num'.AllCnty.txt;
save ${SDATA}mort200`num', replace;
};


foreach num of numlist 9/9 {;
clear;
infix res_status 20 year 115-118
str state_occ 21-22  
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
educ 52-53
str icd10 142-145
str condition1 164-168
str condition2 171-175
str condition3 178-182
str condition4 185-189
str condition5 192-196
str condition6 199-203
str condition7 206-210
str condition8 213-217
str condition9 220-224
str condition10 227-231
str condition11 234-238
str condition12 241-245
str condition13 248-252
str condition14 255-259
str condition15 262-266
str condition16 269-273
str condition17 276-280
str condition18 283-287
str condition19 290-294
str condition20 297-301

str r_condition1 341-345
str r_condition2 346-350
str r_condition3 351-355
str r_condition4 356-360
str r_condition5 361-366
str r_condition6 366-370
str r_condition7 371-375
str r_condition8 376-380
str r_condition9 381-385
str r_condition10 386-390
str r_condition11 391-395
str r_condition12 396-400
str r_condition13 401-405
str r_condition14 406-410
str r_condition15 411-415
str r_condition16 416-420
str r_condition17 421-425
str r_condition18 426-430
str r_condition19 431-435
str r_condition20 436-440


using ${RDATA}/MULT199`num'.AllCnty.txt;
save ${SDATA}mort199`num', replace;
};




foreach num of numlist 98/89 {;
clear;
infix res_status 20 year 115-118
str state_occ 119-120
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
educ 52-53
str icd9 142-145
str condition1 164-167
str condition2 171-174
str condition3 178-181
str condition4 185-188
str condition5 192-195
str condition6 199-202
str condition7 206-209
str condition8 213-216
str condition9 220-223
str condition10 227-230
str condition11 234-237
str condition12 241-244
str condition13 248-251
str condition14 255-258
str condition15 262-265
str condition16 269-272
str condition17 276-279
str condition18 283-286
str condition19 290-293
str condition20 297-300
str nature1 168
str nature2 175
str nature3 182
str nature4 189
str nature5 196
str nature6 203
str nature7 210
str nature8 217
str nature9 224
str nature10 231
str nature11 238
str nature12 245
str nature13 252
str nature14 259
str nature15 266
str nature16 273
str nature17 280
str nature18 287
str nature19 294
str nature20 301

str r_condition1 341-344
str r_condition2 346-349
str r_condition3 351-354
str r_condition4 356-359
str r_condition5 361-364
str r_condition6 366-369
str r_condition7 371-374
str r_condition8 376-379
str r_condition9 381-384
str r_condition10 386-389
str r_condition11 391-394
str r_condition12 396-399
str r_condition13 401-404
str r_condition14 406-409
str r_condition15 411-414
str r_condition16 416-419
str r_condition17 421-424
str r_condition18 426-429
str r_condition19 431-434
str r_condition20 436-439
str r_nature1 345
str r_nature2 350
str r_nature3 355
str r_nature4 360
str r_nature5 365
str r_nature6 370
str r_nature7 375
str r_nature8 380
str r_nature9 385
str r_nature10 390
str r_nature11 395
str r_nature12 400
str r_nature13 405
str r_nature14 410
str r_nature15 415
str r_nature16 420
str r_nature17 425
str r_nature18 430
str r_nature19 435
str r_nature20 440

using ${RDATA}/MULT19`num'.AllCnty.txt;
save ${SDATA}mort19`num', replace;

};

foreach num of numlist 88 83/84 {;
clear;
infix res_status 20 year 1-2
str state_occ 119-120
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
educ 52-53
str icd9 142-145
str condition1 164-167
str condition2 171-174
str condition3 178-181
str condition4 185-188
str condition5 192-195
str condition6 199-202
str condition7 206-209
str condition8 213-216
str condition9 220-223
str condition10 227-230
str condition11 234-237
str condition12 241-244
str condition13 248-251
str condition14 255-258
str condition15 262-265
str condition16 269-272
str condition17 276-279
str condition18 283-286
str condition19 290-293
str condition20 297-300
str nature1 168
str nature2 175
str nature3 182
str nature4 189
str nature5 196
str nature6 203
str nature7 210
str nature8 217
str nature9 224
str nature10 231
str nature11 238
str nature12 245
str nature13 252
str nature14 259
str nature15 266
str nature16 273
str nature17 280
str nature18 287
str nature19 294
str nature20 301

str r_condition1 341-344
str r_condition2 346-349
str r_condition3 351-354
str r_condition4 356-359
str r_condition5 361-364
str r_condition6 366-369
str r_condition7 371-374
str r_condition8 376-379
str r_condition9 381-384
str r_condition10 386-389
str r_condition11 391-394
str r_condition12 396-399
str r_condition13 401-404
str r_condition14 406-409
str r_condition15 411-414
str r_condition16 416-419
str r_condition17 421-424
str r_condition18 426-429
str r_condition19 431-434
str r_condition20 436-439
str r_nature1 345
str r_nature2 350
str r_nature3 355
str r_nature4 360
str r_nature5 365
str r_nature6 370
str r_nature7 375
str r_nature8 380
str r_nature9 385
str r_nature10 390
str r_nature11 395
str r_nature12 400
str r_nature13 405
str r_nature14 410
str r_nature15 415
str r_nature16 420
str r_nature17 425
str r_nature18 430
str r_nature19 435
str r_nature20 440

using ${RDATA}/Vs`num'mort.txt;
save ${SDATA}mort19`num', replace;


};

foreach num of numlist 85/87 {;
clear;
infix res_status 20 year 1-2
str state_occ 119-120
str county_occ 121-123
str state_res 124-125
str county_res 126-128
month 55-56
str sex 59
str race 60-61
str hispanic 82
age_type 64
age 65-66
str marital 77
str birthplace 78-79
educ 52-53
str icd9 142-145
str condition1 164-167
str condition2 171-174
str condition3 178-181
str condition4 185-188
str condition5 192-195
str condition6 199-202
str condition7 206-209
str condition8 213-216
str condition9 220-223
str condition10 227-230
str condition11 234-237
str condition12 241-244
str condition13 248-251
str condition14 255-258
str condition15 262-265
str condition16 269-272
str condition17 276-279
str condition18 283-286
str condition19 290-293
str condition20 297-300
str nature1 168
str nature2 175
str nature3 182
str nature4 189
str nature5 196
str nature6 203
str nature7 210
str nature8 217
str nature9 224
str nature10 231
str nature11 238
str nature12 245
str nature13 252
str nature14 259
str nature15 266
str nature16 273
str nature17 280
str nature18 287
str nature19 294
str nature20 301

str r_condition1 341-344
str r_condition2 346-349
str r_condition3 351-354
str r_condition4 356-359
str r_condition5 361-364
str r_condition6 366-369
str r_condition7 371-374
str r_condition8 376-379
str r_condition9 381-384
str r_condition10 386-389
str r_condition11 391-394
str r_condition12 396-399
str r_condition13 401-404
str r_condition14 406-409
str r_condition15 411-414
str r_condition16 416-419
str r_condition17 421-424
str r_condition18 426-429
str r_condition19 431-434
str r_condition20 436-439
str r_nature1 345
str r_nature2 350
str r_nature3 355
str r_nature4 360
str r_nature5 365
str r_nature6 370
str r_nature7 375
str r_nature8 380
str r_nature9 385
str r_nature10 390
str r_nature11 395
str r_nature12 400
str r_nature13 405
str r_nature14 410
str r_nature15 415
str r_nature16 420
str r_nature17 425
str r_nature18 430
str r_nature19 435
str r_nature20 440


using ${RDATA}/MORT`num'.PUB;
save ${SDATA}mort19`num', replace;


};

# delimit cr
/***** CONSTRUCT OVERDOSE MEASURES ******/

***ICD-10 codes used in 1999+
use ${SDATA}mort1999, clear
for num 2000/2017: append using ${SDATA}mortX
keep if res_<4




gen opioids=0
gen opioids_noT406=0
gen heroin=0
gen overdose=0
gen cocaine=0
gen suicide=0
for num 2/4: gen t40X=0

replace overdose=1 if icd10=="X60"
replace overdose=1 if icd10=="X61"
replace overdose=1 if icd10=="X62"
replace overdose=1 if icd10=="X63"
replace overdose=1 if icd10=="X64"
replace overdose=1 if icd10=="X40"
replace overdose=1 if icd10=="X41"
replace overdose=1 if icd10=="X42"
replace overdose=1 if icd10=="X43"
replace overdose=1 if icd10=="X44"
replace overdose=1 if icd10=="X85"
replace overdose=1 if icd10=="Y10"
replace overdose=1 if icd10=="Y11"
replace overdose=1 if icd10=="Y12"
replace overdose=1 if icd10=="Y13"
replace overdose=1 if icd10=="Y14"



foreach nnn of numlist 60/84 {
	replace suicide=1 if icd10=="X`nnn'"
}
replace suicide=1 if icd10=="Y870"


**chronic liver disease
gen cld=0
replace cld=1 if index(icd10, "K70")>0
replace cld=1 if index(icd10, "K73")>0
replace cld=1 if index(icd10, "K74")>0


gen specified=0
gen unspecified=0
qui foreach num of numlist 1/20 {
	replace unspecified=1  if index(condition`num', "T509")==1
	replace specified=1  if index(condition`num', "T36")==1
	replace specified=1  if index(condition`num', "T37")==1
	replace specified=1  if index(condition`num', "T38")==1
	replace specified=1  if index(condition`num', "T39")==1
	replace specified=1  if index(condition`num', "T4")==1
	for num 0/8: replace specified=1  if index(condition`num', "T50X")==1
	replace opioids=1 if index(condition`num', "T406")==1
	replace opioids=1 if index(condition`num', "T400")==1
	replace opioids=1 if index(condition`num', "T401")==1
	replace opioids=1 if index(condition`num', "T402")==1
	replace opioids=1 if index(condition`num', "T403")==1
	replace opioids=1 if index(condition`num', "T404")==1

	replace opioids_noT406=1 if index(condition`num', "T400")==1
	replace opioids_noT406=1 if index(condition`num', "T401")==1
	replace opioids_noT406=1 if index(condition`num', "T402")==1
	replace opioids_noT406=1 if index(condition`num', "T403")==1
	replace opioids_noT406=1 if index(condition`num', "T404")==1

	replace heroin=1 if condition`num'=="T401"
	replace cocaine=1 if index(condition`num', "T405")==1 
	replace t402=1 if index(condition`num', "T402")==1
	replace t403=1 if index(condition`num', "T403")==1
	replace t404=1 if index(condition`num', "T404")==1

}
replace unspecified=0 if specified==1


for var t4* opioids* cocaine: replace X=X*overdose
gen suicide_overdose=suicide*overdose
gen unspecified_overdose=unspecified*overdose
gen opioids_no_cocaine=opioids*(cocaine==0)
gen cocaine_no_opioids=cocaine*(opioids==0)

gcollapse (sum) unspecified_overdose cld opioids* cocaine*  heroin suicide* overdose t4*, by(year month state_r) fast

statastates, abbreviation(state_r)
tab _m
keep if _m==3 | _m==1
drop _m
rename state_fips stfips
gen tmp=int(real(state_r))
replace stfips=tmp if tmp<.
drop tmp

order state* stfips

save tmp_condensed.dta, replace




***ICD-9 codes
clear all

use ${SDATA}mort1983
replace year=1983

foreach num of numlist 1984/1998 {
	append using ${SDATA}mort`num'
	replace year=`num' if year==.
}

keep if res_<4

*use ICD9 codes
gen overdose=0
gen suicide=0
gen cocaine=0
gen opioids=0
gen cld=0



**nature codes only needed for condition codes above 800
qui foreach num of numlist 1/20 {
	replace overdose=1 if index(r_condition`num', "850")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "851")==1 & r_nature`num'=="0"   & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "852")==1 & r_nature`num'=="0"   & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "853")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "854")==1 & r_nature`num'=="0"   & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "855")==1 & r_nature`num'=="0"   & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "856")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "857")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "858")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9500")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9501")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9502")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9503")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9504")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9505")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9620")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9800")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9801")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9802")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9803")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9804")==1 & r_nature`num'=="0"  & r_condition`num'==icd9
	replace overdose=1 if index(r_condition`num', "9805")==1 & r_nature`num'=="0" & r_condition`num'==icd9

	replace cld=1 if index(r_condition`num', "571")==1 & r_nature`num'=="0" & r_condition`num'==icd9

	replace cocaine=1 if index(r_condition`num', "8552")==1 & r_nature`num'=="0" 
	replace cocaine=1 if index(r_condition`num', "9685")==1 & r_nature`num'=="1"  
	replace cocaine=1 if index(r_condition`num', "9385")==1 & r_nature`num'=="0"  
	replace cocaine=1 if index(r_condition`num', "3056")==1 | index(r_condition`num', "3042")==1 
	replace cocaine=1 if index(r_condition`num', "97081")==1 & r_nature`num'=="1" 

	*r_nature=0 -> E prefix 
	replace opioids=1 if index(r_condition`num', "8500")==1 & r_nature`num'=="0" 
	replace opioids=1 if index(r_condition`num', "8501")==1 & r_nature`num'=="0"
	replace opioids=1 if index(r_condition`num', "8502")==1 & r_nature`num'=="0" 
	replace opioids=1 if index(r_condition`num', "9650")==1 & r_nature`num'=="1" 


}

replace opioids=opioids*overdose
replace cocaine=cocaine*overdose
gen cocaine_no_opioids=cocaine*(opioids==0)
gen opioids_no_cocaine=opioids*(cocaine==0)


qui foreach num of numlist 1/20 {
	replace suicide=1 if index(r_condition`num', "950")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "951")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "952")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "953")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "954")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "955")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "956")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "957")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "958")==1 & r_nature`num'=="0" & r_condition`num'==icd9
	replace suicide=1 if index(r_condition`num', "959")==1 & r_nature`num'=="0" & r_condition`num'==icd9
}
gen suicide_overdose=suicide*overdose



gcollapse (sum)  overdose* cld suicide* opioids* cocaine*, by(year month state_r) fast



gen stfips=int(real(state_r))

save condensed, replace


append using tmp_condensed

gen quarter=1
replace quarter=2 if month>3
replace quarter=3 if month>6
replace quarter=4 if month>9

gcollapse (sum)  overdose* cld suicide* opioids* cocaine*, by(year quarter stfips) fast

save ${SDATA}mortalitydata_qtr, replace

gcollapse (sum)  overdose* cld suicide* opioids* cocaine*, by(year stfips) fast
save ${SDATA}mortalitydata, replace
rm tmp_condensed.dta
rm condensed.dta

